package com.qiyu.bigdata.reportexcel.jobs;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.qiyu.bigdata.utils.HttpUtil;
import com.qiyu.bigdata.reportexcel.entity.BookInfo;
import com.qiyu.bigdata.reportexcel.entity.ReportInfo;
import com.qiyu.bigdata.reportexcel.mapper.ReportMapper;
import com.qiyu.bigdata.reportexcel.service.MailService;
import com.qiyu.bigdata.utils.ExcelUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.client.RestClientException;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
public class ReportJob {

    private final Log logger = LogFactory.getLog(getClass());
    private final Pattern REG_PATTERN = Pattern.compile("#\\{(\\d+):(.+?)(-(.+?))?}");
    private final Map<String, JSONObject> map_visited_interface_code = new HashMap<String, JSONObject>();
    private final List<CellLocate> cellLocates = new ArrayList<>();

    @Value("${report.url}")
    private String host;

    @Value("${report.basedir}")
    private String baseDir;

    public String getHost() {
        return host;
    }

    public String getBaseDir() {
        return baseDir;
    }

    public Map<String, Map<String, Object>> getInterfaceParams() {
        return interfaceParams;
    }

    public List<CellRangeAddress> getMergedRegions() {
        return mergedRegions;
    }

    private Map<String, Map<String, Object>> interfaceParams = null;
    private List<CellRangeAddress> mergedRegions = null;
    private ReportMapper reportMapper;

    private MailService mailSender;


    public ReportJob() {

    }


    public void doJob(int reportId, Map<String, Object> data){
        String period_date=(String)data.get("period_date");
        if(period_date == null){
            period_date = "";
        }

        try{
        List<ReportInfo> reportInfos = reportMapper.findByBookId(reportId);
        if(reportInfos.size()==0){
            logger.warn("未查询到报表配置book_id="+reportId);
            return;
        }
        BookInfo bookInfo = getBookInfo(reportInfos, data);
        logger.debug("报表信息: "+bookInfo);
        setHost(host);
        setInterfaceParams(bookInfo.getInterfaceParams());
//        String templateFilename =String.format("%s/%s",baseDir, bookInfo.getBook_template());
        File templateFile = new File(baseDir, bookInfo.getBook_template());
//        String outputFilename = String.format("%s/%s-%s-%s.xlsx",baseDir,bookInfo.getBook_name(), period_date,new Random().nextInt(1000));
        File outputFile = new File(baseDir, String.format("%s-%s-%s.xlsx", bookInfo.getBook_name(),period_date,new Random().nextInt(1000)));
        reportExcel(templateFile, outputFile);
        String subject=(String)data.get("subject");
        if(subject == null){
        subject = bookInfo.getBook_name();
        }
        mailSender.sendEmail(bookInfo.getBookReceiver().split(","), subject, outputFile);
        boolean is_deleted = outputFile.delete();
        logger.info("邮件发送成功 " + outputFile.getName());
        } catch (Exception e) {
        throw new RuntimeException(e);
    }


    }
    

    public void setInterfaceParams(Map<String, Map<String, Object>> interfaceParams) {
        this.interfaceParams = interfaceParams;
    }

    /**
     * 遍历excel模板文件，填充接口查询的数据
     *
     * @param st sheet表格
     */
    private void traverseSheet(Sheet st) {
        this.mergedRegions = st.getMergedRegions();
//        mergedRegions.forEach(x -> System.out.println(x.formatAsString()));
        Iterator<Row> rowIterator = st.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
//            System.out.print(row);
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String value = ExcelUtil.getValue(cell);
//                System.out.println(value);
                Matcher matcher = REG_PATTERN.matcher(value);
                if (matcher.find()) {
                    String interface_no = matcher.group(1);
                    String interface_code = matcher.group(2);
                    String interface_field = matcher.group(4);
                    String interface_key = st.getSheetName() + interface_code;
//                    System.out.printf("%s, %s, %s\n", interface_no,interface_code, interface_field);
                    if (!map_visited_interface_code.containsKey(interface_key)) {
                        JSONObject jsonData = getInterfaceData(interface_code, interfaceParams.get(interface_key));
                        map_visited_interface_code.put(interface_key, jsonData);
                    }
                    int rowStart = cell.getRowIndex();
                    int cellStart = cell.getColumnIndex();
                    CellLocate cellLocate = new CellLocate();
                    cellLocate.key = interface_key;
                    cellLocate.rowStart = rowStart;
                    cellLocate.columnStart = cellStart;
                    cellLocate.field = interface_field;
                    cellLocates.add(cellLocate);
                }
            }
        }
        for (CellLocate cellLocate : cellLocates) {
            try {
                JSONObject jsonObject = map_visited_interface_code.get(cellLocate.key);
                if(jsonObject == null){
                    logger.error(String.format("未查询到数据{%s}", cellLocate.key));
                    continue;
                }
                fillValueInCell(st, cellLocate.rowStart, cellLocate.columnStart, jsonObject, cellLocate.field);
//                System.out.printf("填充接口数据[%s]\n", cellLocate.key);
                logger.info(String.format("填充接口数据[%s-%s]", cellLocate.key, cellLocate.field));
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        // 每一个sheet保留一次接口数据
        cellLocates.clear();
        map_visited_interface_code.clear();
        mergedRegions = null;
    }

    /**
     * 从指定的cell位置开始写数据
     *
     * @param sheet           sheet
     * @param rowStart        行开始位置
     * @param cellStart       列开始位置
     * @param jsonObject      数据内容，json对象
     * @param interface_field 指定字段值
     */
    private void fillValueInCell(Sheet sheet, int rowStart, int cellStart, JSONObject jsonObject, String interface_field) {
        if ("-1".equals(jsonObject.getString("code"))) {
            return;
        }
        JSONObject property = jsonObject.getJSONObject("property");
        Set<String> columnKeys = property.keySet();
        List<JSONObject> columns = new ArrayList<>(columnKeys.size());
        for (String column : columnKeys) {
            columns.add(property.getJSONObject(column));
        }
        columns.sort(new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject o1, JSONObject o2) {
                return o1.getIntValue("position") - o2.getIntValue("position");
            }
        });

        JSONArray dataList = jsonObject.getJSONArray("data");
        int rowIndex = rowStart;
        int cellIndex = cellStart;
        if (interface_field != null && columnKeys.contains(interface_field)) {
            // 填充指定字段值到cell中
            JSONObject obj = dataList.getJSONObject(0);
            String value = obj.getString(interface_field);
//            System.out.printf("%s = %s \n",interface_field,value);
            Row row = sheet.getRow(rowIndex);
            Cell cell = row.getCell(cellIndex);
            ExcelUtil.setValue(cell, value);
        } else {
            // 遍历数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                JSONObject obj = dataList.getJSONObject(i);
                for (JSONObject column : columns) {
                    if ("0".equals(column.getString("exportFlag"))) {
                        // 跳过不展示的字段
                        continue;
                    }
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null) cell = row.createCell(cellIndex);
                    while (ExcelUtil.isMergedCell(mergedRegions, cell)) {
                        // 跳过合并的单元格
                        cellIndex++;
                        cell = row.getCell(cellIndex);
                        if (cell == null) cell = row.createCell(cellIndex);
                    }

                    String value = obj.getString(column.getString("paraCode"));
                    ExcelUtil.setValue(cell, value);
//                    System.out.printf("(%s, %s)=%s ", rowIndex, cellIndex, value);
                    cellIndex += 1;
                }
                rowIndex++;
                cellIndex = cellStart;
            }
        }
    }

    private Workbook readAllSheet(Workbook wb) throws IOException {
        List<String> sheets = ExcelUtil.getSheets(wb);
        for (String st : sheets) {
            Sheet sheet = wb.getSheet(st);
            traverseSheet(sheet);
        }
        return wb;
    }

    /**
     * 查询接口数据
     *
     * @param code     接口编码
     * @param formData 参数
     */
    private JSONObject getInterfaceData(String code, Map<String, Object> formData) {
        String URL_TEMPLATE = "http://%s/etlctl/reportdata/?interface_code=%s";
        String url = String.format(URL_TEMPLATE, this.host, code);
        JSONObject obj = null;
        if (formData == null) {
            formData = new HashMap<>();
        }
        formData.put("export_type", 2);
        formData.put("operate_type", 2);
        try {
            obj = HttpUtil.doPostByForm(url, formData);
            if (!"success".equals(obj.get("message"))) {
                logger.error(String.format("请求接口{%s}不成功 %s", code, obj));
            } else {
                logger.info(String.format("接口{%s : %s}请求成功", code, formData));
            }
        }catch(RestClientException e){
            logger.error(String.format("接口请求失败: %s", code));
            throw e;
        }
        return obj;
    }

    /**
     * 报表作业任务入口
     */
    public void reportExcel(File templateFile, File outputFile) throws IOException {
        Workbook inputWb = WorkbookFactory.create(new FileInputStream(templateFile));
        Workbook outputWb = readAllSheet(inputWb);
        FileOutputStream fos = new FileOutputStream(outputFile);
        outputWb.write(fos);
        fos.close();
        outputWb.close();
        inputWb.close();
    }

    public BookInfo getBookInfo(List<ReportInfo> reportInfos, Map<String, Object> data) {
        BookInfo bookInfo = new BookInfo();
        Set<String> sheetList = new HashSet<>();
        int bookId = 0;
        String bookName = null;
        String bookTemplate = null;
        String bookReceiver = null;
        Map<String, Map<String, Object>> interfaceParams = new HashMap<>();
        for (ReportInfo reportInfo : reportInfos) {
//            System.out.println(reportInfo);
            String sheetName = reportInfo.getSheet_name();
            sheetList.add(sheetName);
            bookId = reportInfo.getBook_id();
            bookName = reportInfo.getBook_name();
            bookTemplate = reportInfo.getBook_template();
            bookReceiver = reportInfo.getBook_receiver();
            String tableCode = reportInfo.getTable_code();
            HashMap<String, Object> formData = new HashMap<>(data);
            JSONObject json = JSONObject.parseObject(reportInfo.getTable_para());
            for (String s : json.keySet()) {
                formData.put(s, json.get(s));
            }
            String interfaceKey = sheetName + tableCode;
            interfaceParams.put(interfaceKey, formData);
        }
        bookInfo.setBook_id(bookId);
        bookInfo.setBook_name(bookName);
        bookInfo.setBook_template(bookTemplate);
        bookInfo.setBookReceiver(bookReceiver);
        bookInfo.setSheet_name(sheetList);
        bookInfo.setInterfaceParams(interfaceParams);
        return bookInfo;
    }

    @Autowired
    public void setReportMapper(ReportMapper reportMapper) {
        this.reportMapper = reportMapper;
    }


    public void setHost(String host) {
        this.host = host;
    }

    public void setBaseDir(String baseDir){
        this.baseDir = baseDir;
    }
    @Autowired
    public void setMailSender(MailService mailSender) {
        this.mailSender = mailSender;
    }

    private static class CellLocate {
        String key;
        String field;
        int rowStart;
        int columnStart;

    }
}
